*version 8.2
capture clear
capture log close
set more off
set mem 1000m
set mat 800


/*
****IATI full dataset:
clear
insheet using "IATI full dataset.csv"
ren recipientcountry countryname
keep startplanned startactual countryname totalcommitment sectorcode
save "IATI full dataset.dta", replace
*/

/*
clear

use "IATI full dataset.dta"
*year:
gen year=substr(startactual,1,2)
*browse startactual year
replace year="20"+year if substr(startactual,1,1)=="0"|substr(startactual,1,1)=="1"
replace year="19"+year if substr(startactual,1,1)=="9"|substr(startactual,1,1)=="8"|substr(startactual,1,1)=="7"

gen temp=substr(startplanned,1,2)
replace temp="20"+temp if substr(startplanned,1,1)=="0"|substr(startplanned,1,1)=="1"
replace temp="19"+temp if substr(startplanned,1,1)=="9"|substr(startplanned,1,1)=="8"|substr(startplanned,1,1)=="7"

replace year=temp if year==""
drop temp

destring year, replace
sort year

keep countryname year totalcommitment sectorcode

sort countryname year

drop if countryname==""

****now harmonize the name of the countries:

		
replace countryname="Bahamas, The" if countryname=="Bahamas"
replace countryname="Bolivia" if countryname=="Bolivia, Plurinational State Of"
replace countryname="Cabo Verde" if countryname=="Cape Verde"
replace countryname="Congo, Dem. Rep." if countryname=="Congo, The Democratic Republic Of The"
replace countryname="Congo, Rep." if countryname=="Congo"
replace countryname="Cote d'Ivoire" if countryname=="C̣��Te D'Ivoire"
replace countryname="Egypt, Arab Rep." if countryname=="Egypt"
replace countryname="Gambia, The" if countryname=="Gambia"
replace countryname="Hong Kong SAR, China" if countryname=="Hong Kong"
replace countryname="Iran, Islamic Rep." if countryname=="Iran, Islamic Republic Of"
replace countryname="Korea, Dem. People�s Rep." if countryname=="Korea, Democratic People'S Republic Of"
replace countryname="Kyrgyz Republic" if countryname=="Kyrgyzstan"
replace countryname="Lao PDR" if countryname=="Lao People'S Democratic Republic"
replace countryname="Libya" if countryname=="Libyan Arab Jamahiriya"
replace countryname="Macedonia, FYR" if countryname=="Macedonia, The Former Yugoslav Republic Of"
replace countryname="Micronesia, Fed. Sts." if countryname=="Micronesia, Federated States Of"
replace countryname="Moldova" if countryname=="Moldova, Republic Of"
replace countryname="Slovak Republic" if countryname=="Slovakia"
replace countryname="St. Kitts and Nevis" if countryname=="Saint Kitts And Nevis"
replace countryname="St. Lucia" if countryname=="Saint Lucia"
replace countryname="St. Vincent and the Grenadines" if countryname=="Saint Vincent And The Grenadines"
replace countryname="Tanzania" if countryname=="Tanzania, United Republic Of"
replace countryname="Venezuela, RB" if countryname=="Venezuela, Bolivarian Republic Of"
replace countryname="Vietnam" if countryname=="Viet Nam"
replace countryname="West Bank and Gaza" if countryname=="Palestinian Territory, Occupied"
replace countryname="Yemen, Rep." if countryname=="Yemen"

*browse totalcommitment
*sort totalcommitment
*some variables are string, eg "Mixed currency"
destring totalcommitment, force replace

***drop if no info on budget:
drop if totalcommitment==0|totalcommitment==.

sum totalcommitment if countryname!=""&totalcommitment>0
egen sum_totalcommitment=sum(totalcommitment) if countryname!=""
***total: total = 5.65e+12 = 5 Trillion$
*249 Billion dollars on education, much larger than the 3.2 Billion dollars of judicial reforms
drop sum_totalcommitment


***split sectors:
drop if sectorcode==""
split sectorcode, parse(";") gen(sectorcode)

sort countryname year
save "IATI full dataset_clean.dta", replace

*/


***Merging:

use "Enterprise surveys_clean2.dta"
append using "IATI full dataset_clean.dta"

sort countryname year h7a

***Generate a post=0 indicator for the period before the reform:
replace post=post[_n-1] if countryname==countryname[_n-1]&post[_n-1]==0&post==.

***another idea: reform_time_eligible is in between baseline and endline
egen year_baseline=min(year) if h7a!=., by(countryname)
egen year_baseline2=mean(year_baseline), by(countryname)
egen year_endline=max(year) if h7a!=., by(countryname)
egen year_endline2=mean(year_endline), by(countryname)

gen reform_time_eligible=1 if year>=year_baseline2&year<year_endline2
*Choice:
*All reforms implemented in the year of baseline survey are included
*All reforms implemented in the year of endline survey are excluded

*same results with other choices: eg: gen reform_time_eligible=1 if year>year_baseline2&year<year_endline2

sort countryname year

*browse countryname year court_good_small totalcommitment sectorcode1 sectorcode2 post reform_time_eligible if countryname=="Kenya"

***this creates dummies equal to 1 if the sector reform concerns a specific sector:
*we start with education: 
*Education: 11 (111 Education, 112 Basic education, 113 Secondary education, 114 Post-secondary education)

forvalues i=1(1) 29{
	gen temp`i'=1 if reform_time_eligible==1&substr(sectorcode`i',1,2)=="11"
	}
	
*we sum the dummies:	
egen temp_total=rowtotal(temp*)
*this creates temp_dum: equal to 1 if there is a reform
gen temp_dum=1 if temp_total>0&temp_total!=.
*we sum the budget for those reforms only
egen temp=sum(totalcommitment) if temp_dum==1, by (countryname)
egen reform_edu=mean(temp), by (countryname)
drop temp*



*****Now here: do health: Health: 12 13 14
* (121 Health, general, 122 Basic health, 130	 Population policies/ programmes and reproductive health, 140 Water and sanitation):

forvalues i=1(1) 29{
	gen temp`i'=1 if reform_time_eligible==1&(substr(sectorcode`i',1,2)=="12"|substr(sectorcode`i',1,2)=="13"|substr(sectorcode`i',1,2)=="14")
	}
	

egen temp_total=rowtotal(temp*)
gen temp_dum=1 if temp_total>0&temp_total!=.
egen temp=sum(totalcommitment) if temp_dum==1, by (countryname)
egen reform_health=mean(temp), by (countryname)
drop temp*

*Judiciary: 15130

forvalues i=1(1) 29{
	gen temp`i'=1 if reform_time_eligible==1&(substr(sectorcode`i',1,5)=="15130")
	}
	

egen temp_total=rowtotal(temp*)
gen temp_dum=1 if temp_total>0&temp_total!=.
egen temp=sum(totalcommitment) if temp_dum==1, by (countryname)
egen reform_jud2=mean(temp), by (countryname)
drop temp*

****Tax:
*15114: Domestic Revenue Mobilisation: Support to domestic revenue mobilisation/tax policy, analysis and administration as well as non-tax public revenue, which includes work with ministries of finance, line ministries, revenue authorities or other local, regional or national public bodies. (Use code 16010 for social security and other social protection.)
*15116	Tax collection	Operation of the inland revenue authority.
*15155	Tax policy and administration support
*15156	Other non-tax revenue mobilisation	Non-tax public revenue, which includes line ministries, revenue authorities or other local, regional or national public bodies.

forvalues i=1(1) 29{
	gen temp`i'=1 if reform_time_eligible==1&(substr(sectorcode`i',1,5)=="15114"|substr(sectorcode`i',1,5)=="15116"|substr(sectorcode`i',1,5)=="15155"|substr(sectorcode`i',1,5)=="15156")
	}
	

egen temp_total=rowtotal(temp*)
gen temp_dum=1 if temp_total>0&temp_total!=.
egen temp=sum(totalcommitment) if temp_dum==1, by (countryname)
egen reform_tax=mean(temp), by (countryname)
drop temp*

*Conflict:
*15220	Civilian peace-building, conflict prevention and resolution
*15240	Reintegration and SALW control
*15261	Child soldiers (Prevention and demobilisation)

forvalues i=1(1) 29{
	gen temp`i'=1 if reform_time_eligible==1&(substr(sectorcode`i',1,5)=="15220"|substr(sectorcode`i',1,5)=="15240"|substr(sectorcode`i',1,5)=="15261")
	}
	

egen temp_total=rowtotal(temp*)
gen temp_dum=1 if temp_total>0&temp_total!=.
egen temp=sum(totalcommitment) if temp_dum==1, by (countryname)
egen reform_confl=mean(temp), by (countryname)
drop temp*

*Anti-corruption:
*15113	Anti-corruption organisations and institutions
*15120	Public sector financial management	Strengthening financial and managerial accountability; public expenditure management; improving financial management systems; tax assessment procedures; budget drafting; field auditing; measures against waste, fraud and corruption.

forvalues i=1(1) 29{
	gen temp`i'=1 if reform_time_eligible==1&(substr(sectorcode`i',1,5)=="15113"|substr(sectorcode`i',1,5)=="15120")
	}
	

egen temp_total=rowtotal(temp*)
gen temp_dum=1 if temp_total>0&temp_total!=.
egen temp=sum(totalcommitment) if temp_dum==1, by (countryname)
egen reform_corrup=mean(temp), by (countryname)
drop temp*

****checks on executive: parliament, media

*parliament:
*15140	Government administration	Systems of government including parliament, local government, decentralisation; civil service and civil service reform.
*15152	Legislatures and political parties	Assistance to strengthen key functions of legislatures/ parliaments including subnational assemblies and councils (representation; oversight; legislation), such as improving the capacity of legislative bodies, improving legislatures� committees and administrative procedures,; research and information management systems; providing training programmes for legislators and support personnel. Assistance to political parties and strengthening of party systems.

forvalues i=1(1) 29{
	gen temp`i'=1 if reform_time_eligible==1&(substr(sectorcode`i',1,5)=="15140"|substr(sectorcode`i',1,5)=="15152")
	}
	

egen temp_total=rowtotal(temp*)
gen temp_dum=1 if temp_total>0&temp_total!=.
egen temp=sum(totalcommitment) if temp_dum==1, by (countryname)
egen reform_parlia=mean(temp), by (countryname)
drop temp*


*Elections:
*15151	Elections	Electoral management bodies and processes, election observation, voters� education.
*15161	Elections	Electoral assistance and monitoring, voters� education

forvalues i=1(1) 29{
	gen temp`i'=1 if reform_time_eligible==1&(substr(sectorcode`i',1,5)=="15151"|substr(sectorcode`i',1,5)=="15161")
	}
	

egen temp_total=rowtotal(temp*)
gen temp_dum=1 if temp_total>0&temp_total!=.
egen temp=sum(totalcommitment) if temp_dum==1, by (countryname)
egen reform_elec=mean(temp), by (countryname)
drop temp*

*Media:
*15153	Media and free flow of information
*15163	Free flow of information	Uncensored flow of information on public issues, including activities that increase the professionalism, skills and integrity of the print and broadcast media (e.g. training of journalists).

forvalues i=1(1) 29{
	gen temp`i'=1 if reform_time_eligible==1&(substr(sectorcode`i',1,5)=="15153"|substr(sectorcode`i',1,5)=="15163")
	}
	

egen temp_total=rowtotal(temp*)
gen temp_dum=1 if temp_total>0&temp_total!=.
egen temp=sum(totalcommitment) if temp_dum==1, by (countryname)
egen reform_media=mean(temp), by (countryname)
drop temp*



*Transport: 21 (road construction, road maintenance, Rail transport, Water transport, Air transport)


forvalues i=1(1) 29{
	gen temp`i'=1 if reform_time_eligible==1&substr(sectorcode`i',1,2)=="21"
	}
	
egen temp_total=rowtotal(temp*)
gen temp_dum=1 if temp_total>0&temp_total!=.
egen temp=sum(totalcommitment) if temp_dum==1, by (countryname)
egen reform_trans=mean(temp), by (countryname)
drop temp*

*Energy: 23 (Oil, Gas, Coal, Nuclear, Hydro-electric powrer plants, Geothermal, Solar, Wind, Ocean energy)




forvalues i=1(1) 29{
	gen temp`i'=1 if reform_time_eligible==1&substr(sectorcode`i',1,2)=="23"
	}
	
egen temp_total=rowtotal(temp*)
gen temp_dum=1 if temp_total>0&temp_total!=.
egen temp=sum(totalcommitment) if temp_dum==1, by (countryname)
egen reform_nrj=mean(temp), by (countryname)
drop temp*


*Banking: 24
/*
24010	Financial policy and administrative management	Finance sector policy, planning and programmes; institution capacity building and advice; financial markets and systems.
24020	Monetary institutions	Central banks.
24030	Formal sector financial intermediaries	All formal sector financial intermediaries; credit lines; insurance, leasing, venture capital, etc. (except when focused on only one sector).
24040	Informal/semi-formal financial intermediaries	Micro credit, savings and credit co-operatives etc.
24081	Education/training in banking and financial services	
*/

forvalues i=1(1) 29{
	gen temp`i'=1 if reform_time_eligible==1&substr(sectorcode`i',1,2)=="24"
	}
	
egen temp_total=rowtotal(temp*)
gen temp_dum=1 if temp_total>0&temp_total!=.
egen temp=sum(totalcommitment) if temp_dum==1, by (countryname)
egen reform_bank=mean(temp), by (countryname)
drop temp*


*32: support to industry:
*and:
*Business support:
*25010	Business support services and institutions	Support to trade and business associations, chambers of commerce; legal and regulatory reform aimed at improving business and investment climate; private sector institution capacity building and advice; trade information; public-private sector networking including trade fairs; e-commerce. Where sector cannot be specified: general support to private sector enterprises (in particular, use code 32130 for enterprises in the industrial sector).
*25020	Privatisation	When sector cannot be specified. Including general state enterprise restructuring or demonopolisation programmes; planning, programming, advice.


forvalues i=1(1) 29{
	gen temp`i'=1 if reform_time_eligible==1&(substr(sectorcode`i',1,2)=="32"|substr(sectorcode`i',1,2)=="25")
	}
	
egen temp_total=rowtotal(temp*)
gen temp_dum=1 if temp_total>0&temp_total!=.
egen temp=sum(totalcommitment) if temp_dum==1, by (countryname)
egen reform_indus=mean(temp), by (countryname)
drop temp*


*TRADE:
/*
33110	Trade policy and administrative Management
33120	Trade facilitation
33130	Regional trade agreements (RTAs)
33140	Multilateral trade negotiations
33150	Trade-related adjustment
33181	Trade education/training
33210	Tourism policy and administrative management
*/

forvalues i=1(1) 29{
	gen temp`i'=1 if reform_time_eligible==1&substr(sectorcode`i',1,2)=="33"
	}
	
egen temp_total=rowtotal(temp*)
gen temp_dum=1 if temp_total>0&temp_total!=.
egen temp=sum(totalcommitment) if temp_dum==1, by (countryname)
egen reform_trade=mean(temp), by (countryname)
drop temp*

*Debt:
/*
60010	Action relating to debt
60020	Debt forgiveness
60030	Relief of multilateral debt
60040	Rescheduling and refinancing
60061	Debt for development swap
60062	Other debt swap
60063	Debt buy-back
*/

forvalues i=1(1) 29{
	gen temp`i'=1 if reform_time_eligible==1&substr(sectorcode`i',1,2)=="60"
	}
	
egen temp_total=rowtotal(temp*)
gen temp_dum=1 if temp_total>0&temp_total!=.
egen temp=sum(totalcommitment) if temp_dum==1, by (countryname)
egen reform_debt=mean(temp), by (countryname)
drop temp*




**keep only when we have entreprise surveys data
*the above commands are egen, they generate variables for the whole country
*The below command only keeps the years in which there is data on the courts
keep if h7a~=.
keep countryname year post reform_*

sort countryname year
*Calculate duration between two waves:
gen duration=year-year[_n-1] if countryname==countryname[_n-1]
replace duration=duration[_n+1] if countryname==countryname[_n+1]


local list_reform_type edu health jud2 tax confl trans nrj bank indus trade debt corrup parlia elec media



foreach var of local list_reform_type{

***replace budget by zero if no reform:
replace reform_`var'=0 if reform_`var'==.
***Budget reforms per year:
sort countryname year
replace reform_`var'=reform_`var'[_n-1] if countryname==countryname[_n-1]
replace reform_`var'=reform_`var'/duration
*In million dollars:
replace reform_`var'=reform_`var'/1000000
*replace budget by 0 in the baseline:
replace reform_`var'=0 if post==0
gen reform_`var'_post=reform_`var'*post


}

*browse countryname year post reform_edu



sort countryname year
merge countryname year using "GDP per capita_clean2.dta"
***drop the observations in World Development indicators but not in Entreprise surveys
drop if _merge==2
drop _merge


local list_reform_type edu health jud2 tax confl trans nrj bank indus trade debt corrup parlia elec media
foreach var of local list_reform_type{

	gen reform_`var'_gdp_post=reform_`var'_post*1000000/gdppppconstant2011internationaln*100
	gen reform_`var'_percap_post=reform_`var'_post*1000000/populationtotalsppoptotl

}

keep countryname year post reform_*


label variable reform_edu_gdp "Budget of education reforms (as a fraction of GDP)"
label variable reform_edu_percap "Budget of education reforms per capita"
label variable reform_edu_gdp_post "Budget of education reforms (as a fraction of GDP) * Post"


label variable reform_edu_gdp_post "Education * Post"
label variable reform_health_gdp_post "Health * Post"
label variable reform_trans_gdp_post "Transport * Post"
label variable reform_nrj_gdp_post "Energy * Post"
label variable reform_bank_gdp_post "Bank * Post"
label variable reform_indus_gdp_post "Support Industry * Post"
label variable reform_trade_gdp_post "Trade * Post"
label variable reform_debt_gdp_post "Debt * Post"
label variable reform_corrup_gdp_post "Corruption * Post"
label variable reform_tax_gdp_post "Tax * Post"
label variable reform_confl_gdp_post "Conflict * Post"
label variable reform_parlia_gdp_post "Parliament * Post"
label variable reform_elec_gdp_post "Elections * Post"
label variable reform_media_gdp_post "Media * Post"


sort countryname year
save "Enterprise surveys_Other_reforms.dta", replace

exit


